﻿/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * All rights reserved.
 *
 * EPPlus is an Open Source project provided under the
 * GNU General Public License (GPL) as published by the
 * Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 *
 * EPPlus provides server-side generation of Excel 2007 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 *
 *
 * The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * The code for this project may be used and redistributed by any means PROVIDING it is
 * not sold for profit without the author's written consent, and providing that this notice
 * and the author's name and all copyright notices remain intact.
 *
 * All code and executables are provided "as is" with no warranty either express or implied.
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 *
 * Code change notes:
 *
 * Author							Change						Date
 *******************************************************************************
 * Eyal Seagull				Added							2012-04-03
 *******************************************************************************/

using System.Drawing;
using System.IO;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OfficeOpenXml;
using OfficeOpenXml.ConditionalFormatting;
using OfficeOpenXml.Style;

namespace EPPlus.Core.Tests.SampleApp
{
    [TestClass]
    public class Sample14
    {
        /// <summary>
        /// Sample 14 - Conditional formatting example
        /// </summary>
        [TestMethod]
        public void RunSample14()
        {
            FileInfo newFile = new FileInfo(Path.Combine("bin", "sample14.xlsx"));
            if(newFile.Exists)
            {
                newFile.Delete();
            }

            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                // add a new worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Conditional Formatting");

                // Create 4 columns of samples data
                for (int col = 1; col < 10; col++)
                {
                    // Add the headers
                    worksheet.Cells[1, col].Value = "Sample " + col;

                    for (int row = 2; row < 21; row++)
                    {
                        // Add some items...
                        worksheet.Cells[row, col].Value = row;
                    }
                }

                // -------------------------------------------------------------------
                // TwoColorScale Conditional Formatting example
                // -------------------------------------------------------------------
                ExcelAddress cfAddress1 = new ExcelAddress("A2:A10");
                var cfRule1 = worksheet.ConditionalFormatting.AddTwoColorScale(cfAddress1);

                // Now, lets change some properties:
                cfRule1.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
                cfRule1.LowValue.Value = 4;
                cfRule1.LowValue.Color = ColorTranslator.FromHtml("#FFFFEB84");
                cfRule1.HighValue.Type = eExcelConditionalFormattingValueObjectType.Formula;
                cfRule1.HighValue.Formula = "IF($G$1=\"A</x:&'cfRule>\",1,5)";
                cfRule1.StopIfTrue = true;
                cfRule1.Style.Font.Bold = true;

                // But others you can't (readonly)
                // cfRule1.Type = eExcelConditionalFormattingRuleType.ThreeColorScale;

                // -------------------------------------------------------------------
                // ThreeColorScale Conditional Formatting example
                // -------------------------------------------------------------------
                ExcelAddress cfAddress2 = new ExcelAddress(2, 2, 10, 2);  //="B2:B10"
                var cfRule2 = worksheet.ConditionalFormatting.AddThreeColorScale(cfAddress2);

                // Changing some properties again
                cfRule2.Priority = 1;
                cfRule2.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Percentile;
                cfRule2.MiddleValue.Value = 30;
                cfRule2.StopIfTrue = true;

                // You can access a rule by its Priority
                var cfRule2Priority = cfRule2.Priority;
                var cfRule2_1 = worksheet.ConditionalFormatting.RulesByPriority(cfRule2Priority);

                // And you can even change the rule's Address
                cfRule2_1.Address = new ExcelAddress("Z1:Z3");

                // -------------------------------------------------------------------
                // Adding another ThreeColorScale in a different way (observe that we are
                // pointing to the same range as the first rule we entered. Excel allows it to
                // happen and group the rules in one <conditionalFormatting> node)
                // -------------------------------------------------------------------
                var cfRule3 = worksheet.Cells[cfAddress1.Address].ConditionalFormatting.AddThreeColorScale();
                cfRule3.LowValue.Color = Color.LemonChiffon;

                // -------------------------------------------------------------------
                // Change the rules priorities to change their execution order
                // -------------------------------------------------------------------
                cfRule3.Priority = 1;
                cfRule1.Priority = 2;
                cfRule2.Priority = 3;

                // -------------------------------------------------------------------
                // Create an Above Average rule
                // -------------------------------------------------------------------
                var cfRule5 = worksheet.ConditionalFormatting.AddAboveAverage(
                  new ExcelAddress("B11:B20"));
                cfRule5.Style.Font.Bold = true;
                cfRule5.Style.Font.Color.Color = Color.Red;
                cfRule5.Style.Font.Strike = true;

                // -------------------------------------------------------------------
                // Create an Above Or Equal Average rule
                // -------------------------------------------------------------------
                var cfRule6 = worksheet.ConditionalFormatting.AddAboveOrEqualAverage(
                  new ExcelAddress("B11:B20"));

                // -------------------------------------------------------------------
                // Create a Below Average rule
                // -------------------------------------------------------------------
                var cfRule7 = worksheet.ConditionalFormatting.AddBelowAverage(
                  new ExcelAddress("B11:B20"));

                // -------------------------------------------------------------------
                // Create a Below Or Equal Average rule
                // -------------------------------------------------------------------
                var cfRule8 = worksheet.ConditionalFormatting.AddBelowOrEqualAverage(
                  new ExcelAddress("B11:B20"));

                // -------------------------------------------------------------------
                // Create a Above StdDev rule
                // -------------------------------------------------------------------
                var cfRule9 = worksheet.ConditionalFormatting.AddAboveStdDev(
                  new ExcelAddress("B11:B20"));
                cfRule9.StdDev = 0;

                // -------------------------------------------------------------------
                // Create a Below StdDev rule
                // -------------------------------------------------------------------
                var cfRule10 = worksheet.ConditionalFormatting.AddBelowStdDev(
                  new ExcelAddress("B11:B20"));

                cfRule10.StdDev = 2;

                // -------------------------------------------------------------------
                // Create a Bottom rule
                // -------------------------------------------------------------------
                var cfRule11 = worksheet.ConditionalFormatting.AddBottom(
                  new ExcelAddress("B11:B20"));

                cfRule11.Rank = 4;

                // -------------------------------------------------------------------
                // Create a Bottom Percent rule
                // -------------------------------------------------------------------
                var cfRule12 = worksheet.ConditionalFormatting.AddBottomPercent(
                  new ExcelAddress("B11:B20"));

                cfRule12.Rank = 15;

                // -------------------------------------------------------------------
                // Create a Top rule
                // -------------------------------------------------------------------
                var cfRule13 = worksheet.ConditionalFormatting.AddTop(
                  new ExcelAddress("B11:B20"));

                // -------------------------------------------------------------------
                // Create a Top Percent rule
                // -------------------------------------------------------------------
                var cfRule14 = worksheet.ConditionalFormatting.AddTopPercent(
                  new ExcelAddress("B11:B20"));

                cfRule14.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                cfRule14.Style.Border.Left.Color.Theme = 3;
                cfRule14.Style.Border.Bottom.Style = ExcelBorderStyle.DashDot;
                cfRule14.Style.Border.Bottom.Color.Index = 8;
                cfRule14.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                cfRule14.Style.Border.Right.Color.Color = Color.Blue;
                cfRule14.Style.Border.Top.Style = ExcelBorderStyle.Hair;
                cfRule14.Style.Border.Top.Color.Auto = true;

                // -------------------------------------------------------------------
                // Create a Last 7 Days rule
                // -------------------------------------------------------------------
                ExcelAddress timePeriodAddress = new ExcelAddress("D21:G34 C11:C20");
                var cfRule15 = worksheet.ConditionalFormatting.AddLast7Days(
                  timePeriodAddress);

                cfRule15.Style.Fill.PatternType = ExcelFillStyle.LightTrellis;
                cfRule15.Style.Fill.PatternColor.Color = Color.BurlyWood;
                cfRule15.Style.Fill.BackgroundColor.Color = Color.LightCyan;

                // -------------------------------------------------------------------
                // Create a Last Month rule
                // -------------------------------------------------------------------
                var cfRule16 = worksheet.ConditionalFormatting.AddLastMonth(
                  timePeriodAddress);

                cfRule16.Style.NumberFormat.Format = "YYYY";
                // -------------------------------------------------------------------
                // Create a Last Week rule
                // -------------------------------------------------------------------
                var cfRule17 = worksheet.ConditionalFormatting.AddLastWeek(
                  timePeriodAddress);
                cfRule17.Style.NumberFormat.Format = "YYYY";

                // -------------------------------------------------------------------
                // Create a Next Month rule
                // -------------------------------------------------------------------
                var cfRule18 = worksheet.ConditionalFormatting.AddNextMonth(
                  timePeriodAddress);

                // -------------------------------------------------------------------
                // Create a Next Week rule
                // -------------------------------------------------------------------
                var cfRule19 = worksheet.ConditionalFormatting.AddNextWeek(
                  timePeriodAddress);

                // -------------------------------------------------------------------
                // Create a This Month rule
                // -------------------------------------------------------------------
                var cfRule20 = worksheet.ConditionalFormatting.AddThisMonth(
                  timePeriodAddress);

                // -------------------------------------------------------------------
                // Create a This Week rule
                // -------------------------------------------------------------------
                var cfRule21 = worksheet.ConditionalFormatting.AddThisWeek(
                  timePeriodAddress);

                // -------------------------------------------------------------------
                // Create a Today rule
                // -------------------------------------------------------------------
                var cfRule22 = worksheet.ConditionalFormatting.AddToday(
                  timePeriodAddress);

                // -------------------------------------------------------------------
                // Create a Tomorrow rule
                // -------------------------------------------------------------------
                var cfRule23 = worksheet.ConditionalFormatting.AddTomorrow(
                  timePeriodAddress);

                // -------------------------------------------------------------------
                // Create a Yesterday rule
                // -------------------------------------------------------------------
                var cfRule24 = worksheet.ConditionalFormatting.AddYesterday(
                  timePeriodAddress);

                // -------------------------------------------------------------------
                // Create a BeginsWith rule
                // -------------------------------------------------------------------
                ExcelAddress cellIsAddress = new ExcelAddress("E11:E20");
                var cfRule25 = worksheet.ConditionalFormatting.AddBeginsWith(
                  cellIsAddress);

                cfRule25.Text = "SearchMe";

                // -------------------------------------------------------------------
                // Create a Between rule
                // -------------------------------------------------------------------
                var cfRule26 = worksheet.ConditionalFormatting.AddBetween(
                  cellIsAddress);

                cfRule26.Formula = "IF(E11>5,10,20)";
                cfRule26.Formula2 = "IF(E11>5,30,50)";

                // -------------------------------------------------------------------
                // Create a ContainsBlanks rule
                // -------------------------------------------------------------------
                var cfRule27 = worksheet.ConditionalFormatting.AddContainsBlanks(
                  cellIsAddress);

                // -------------------------------------------------------------------
                // Create a ContainsErrors rule
                // -------------------------------------------------------------------
                var cfRule28 = worksheet.ConditionalFormatting.AddContainsErrors(
                  cellIsAddress);

                // -------------------------------------------------------------------
                // Create a ContainsText rule
                // -------------------------------------------------------------------
                var cfRule29 = worksheet.ConditionalFormatting.AddContainsText(
                  cellIsAddress);

                cfRule29.Text = "Me";

                // -------------------------------------------------------------------
                // Create a DuplicateValues rule
                // -------------------------------------------------------------------
                var cfRule30 = worksheet.ConditionalFormatting.AddDuplicateValues(
                  cellIsAddress);

                // -------------------------------------------------------------------
                // Create an EndsWith rule
                // -------------------------------------------------------------------
                var cfRule31 = worksheet.ConditionalFormatting.AddEndsWith(
                  cellIsAddress);

                cfRule31.Text = "EndText";

                // -------------------------------------------------------------------
                // Create an Equal rule
                // -------------------------------------------------------------------
                var cfRule32 = worksheet.ConditionalFormatting.AddEqual(
                  cellIsAddress);

                cfRule32.Formula = "6";

                // -------------------------------------------------------------------
                // Create an Expression rule
                // -------------------------------------------------------------------
                var cfRule33 = worksheet.ConditionalFormatting.AddExpression(
                  cellIsAddress);

                cfRule33.Formula = "E11=E12";

                // -------------------------------------------------------------------
                // Create a GreaterThan rule
                // -------------------------------------------------------------------
                var cfRule34 = worksheet.ConditionalFormatting.AddGreaterThan(
                  cellIsAddress);

                cfRule34.Formula = "SE(E11<10,10,65)";

                // -------------------------------------------------------------------
                // Create a GreaterThanOrEqual rule
                // -------------------------------------------------------------------
                var cfRule35 = worksheet.ConditionalFormatting.AddGreaterThanOrEqual(
                  cellIsAddress);

                cfRule35.Formula = "35";

                // -------------------------------------------------------------------
                // Create a LessThan rule
                // -------------------------------------------------------------------
                var cfRule36 = worksheet.ConditionalFormatting.AddLessThan(
                  cellIsAddress);

                cfRule36.Formula = "36";

                // -------------------------------------------------------------------
                // Create a LessThanOrEqual rule
                // -------------------------------------------------------------------
                var cfRule37 = worksheet.ConditionalFormatting.AddLessThanOrEqual(
                  cellIsAddress);

                cfRule37.Formula = "37";

                // -------------------------------------------------------------------
                // Create a NotBetween rule
                // -------------------------------------------------------------------
                var cfRule38 = worksheet.ConditionalFormatting.AddNotBetween(
                  cellIsAddress);

                cfRule38.Formula = "333";
                cfRule38.Formula2 = "999";

                // -------------------------------------------------------------------
                // Create a NotContainsBlanks rule
                // -------------------------------------------------------------------
                var cfRule39 = worksheet.ConditionalFormatting.AddNotContainsBlanks(
                  cellIsAddress);

                // -------------------------------------------------------------------
                // Create a NotContainsErrors rule
                // -------------------------------------------------------------------
                var cfRule40 = worksheet.ConditionalFormatting.AddNotContainsErrors(
                  cellIsAddress);

                // -------------------------------------------------------------------
                // Create a NotContainsText rule
                // -------------------------------------------------------------------
                var cfRule41 = worksheet.ConditionalFormatting.AddNotContainsText(
                  cellIsAddress);

                cfRule41.Text = "NotMe";

                // -------------------------------------------------------------------
                // Create an NotEqual rule
                // -------------------------------------------------------------------
                var cfRule42 = worksheet.ConditionalFormatting.AddNotEqual(
                  cellIsAddress);

                cfRule42.Formula = "14";

                ExcelAddress cfAddress43 = new ExcelAddress("G2:G10");
                var cfRule43 = worksheet.ConditionalFormatting.AddThreeIconSet(cfAddress43, eExcelconditionalFormatting3IconsSetType.TrafficLights1);

                ExcelAddress cfAddress44 = new ExcelAddress("H2:H10");
                var cfRule44 = worksheet.ConditionalFormatting.AddDatabar(cfAddress44, Color.DarkBlue);

                // -----------------------------------------------------------
                // Removing Conditional Formatting rules
                // -----------------------------------------------------------
                // Remove one Rule by its object
                //worksheet.ConditionalFormatting.Remove(cfRule1);

                // Remove one Rule by index
                //worksheet.ConditionalFormatting.RemoveAt(1);

                // Remove one Rule by its Priority
                //worksheet.ConditionalFormatting.RemoveByPriority(2);

                // Remove all the Rules
                //worksheet.ConditionalFormatting.RemoveAll();

                // set some document properties
                package.Workbook.Properties.Title = "Conditional Formatting";
                package.Workbook.Properties.Author = "Eyal Seagull";
                package.Workbook.Properties.Comments = "This sample demonstrates how to add Conditional Formatting to an Excel 2007 worksheet using EPPlus";

                // set some custom property values
                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Eyal Seagull");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

                // save our new workbook and we are done!
                package.Save();
            }
        }
    }
}